from mydb.odbc import atable

#odbc=base()
#odbc.dsn='payroll'
#odbc.charset='cp866'
#gen=odbc.generate('SELECT * FROM Anketa')
#odbc.commit('CREATE TABLE E:\Py\MyPy\progs\payroll\test.dbf (test varchar(10))')


m06_err=atable()
m06_err.table.base.dsn='cen'
m06_err.name='m06_err'

mat_obr=atable()
mat_obr.table.base.dsn='cen'

def zaks():
	"This function returns zaks to add in spi_zak or correct"
	spi_zak=atable()
	spi_zak.table.base.dsn='alldata'
	spi_zak.name='spi_zak'
	
	
	spi_g=spi_zak.generate(['ALLTRIM(n_zak)'])
	spi_list=[]
	for zak in spi_g:
		spi_list+=[zak[0]]

	err_g=m06_err.generate(['ALLTRIM(zak)'],[['cvo','=','"50"']])
	err_list=[]
	for zak in err_g:
		err_list+=[zak[0]]

	result=[]
	for err in err_list:
		if err not in spi_list:
			result+=[err]
	
	print(result)
	err_list=[]
	
	for zak in result:
		err_g=m06_err.table.base.generate("SELECT blk_no, nom_nom, sklad, ceh_doc, zak, ei, cena, cvo, kol_vo, summa FROM m06_err WHERE zak='"+zak+"'")
		for err in err_g:
			err_list+=[err]

	return err_list
	
	
def noms():
	"This function returns noms to add in cenn or correct"
	cen_pc=atable()
	cen_pc.table.base.dsn='alldata'
	cen_pc.name='cen_pc'
	nom_g=cen_pc.generate(['ALLTRIM(nom_nom)'])
	nom_list=[]
	for nom in nom_g:
		nom_list+=[nom[0]]
		
	err_g=m06_err.generate(['ALLTRIM(nom_nom)'])
	err_list=[]
	for nom in err_g:
		err_list+=[nom[0]]
	
	result=[]
	for err in err_list:
		if err not in nom_list:
			result+=[err]
	
	print(result)
	
	err_list=[]
	
	for nom in result:
		err_g=m06_err.table.base.generate("SELECT blk_no, nom_nom, sklad, ceh_doc, zak, ei, cena, cvo, kol_vo, summa FROM m06_err WHERE nom_nom='"+nom+"'")
		
		for err in err_g:
			err_list+=[err]

	return err_list
	
	

def fifty2():
	"This function returns noms where price of 52!= price of 32 operations"
	mat_obr=atable()
	mat_obr.table.base.dsn='cen'
	#nom_g=mat_obr.table.base.generate("SELECT DISTINCT a.nom_nom FROM mat_obr a, mat_obr b WHERE a.nom_nom=b.nom_nom AND a.vo='32' AND b.vo='52' AND a.kol_d=-1*b.kol_d AND a.cen_zav<>b.cen_zav AND a.ceh=b.sklad AND b.ceh=a.sklad")
	nom_g=mat_obr.table.base.generate("SELECT DISTINCT a.nom_nom FROM mat_obr a, mat_obr b WHERE a.nom_nom=b.nom_nom AND a.vo='32' AND b.vo='52' AND a.ceh=b.sklad AND b.ceh=a.sklad")
	nom_list=[]
	for nom in nom_g:
		nom_list+=[nom[0]]
	
	err_list1=[]
	for nom in nom_list:
		err_g=mat_obr.table.base.generate("SELECT nom_nom, SUM(kol_d), SUM(sum_d) FROM mat_obr WHERE (vo='52' OR vo='32') AND nom_nom='"+nom+"' GROUP BY nom_nom HAVING SUM(kol_d)<>0 OR SUM(sum_d)<>0")
		for err in err_g:
			err_list1+=[err[0]]
	
	err_list=[]
	for nom in err_list1:
		err_g=mat_obr.table.base.generate("SELECT blk_no, nom_nom, sklad, ceh, zak, ei, cen_zav, vo, kol, sum_zav, ost_ksu, sum_post, sum_chet, kol_tn, cen_post, kol_d, sum_d, kol_i, sum_i, n_ksu, ost_ksud FROM mat_obr WHERE nom_nom='"+nom+"'")
		for err in err_g:
			err_list+=[err]

	return err_list

def fifty2_noms():
	"This function returns noms where price of 52!= price of 32 operations"
	mat_obr=atable()
	mat_obr.table.base.dsn='cen'
	#nom_g=mat_obr.table.base.generate("SELECT DISTINCT a.nom_nom FROM mat_obr a, mat_obr b WHERE a.nom_nom=b.nom_nom AND a.vo='32' AND b.vo='52' AND a.kol_d=-1*b.kol_d AND a.cen_zav<>b.cen_zav AND a.ceh=b.sklad AND b.ceh=a.sklad")
	nom_g=mat_obr.table.base.generate("SELECT DISTINCT a.nom_nom FROM mat_obr a, mat_obr b WHERE a.nom_nom=b.nom_nom AND a.vo='32' AND b.vo='52' AND a.ceh=b.sklad AND b.ceh=a.sklad")
	nom_list=[]
	for nom in nom_g:
		nom_list+=[nom[0]]
	
	err_list1=[]
	for nom in nom_list:
		err_g=mat_obr.table.base.generate("SELECT nom_nom, SUM(kol_d), SUM(sum_d) FROM mat_obr WHERE (vo='52' OR vo='32') AND nom_nom='"+nom+"' GROUP BY nom_nom HAVING SUM(kol_d)<>0 OR SUM(sum_d)<>0")
		for err in err_g:
			err_list1+=[err[0]]

	return err_list1

def saldo():
	"This function returns noms where saldo is corrupted"
	mat_obr=atable()
	mat_obr.table.base.dsn='cen'
	#nom_g=mat_obr.table.base.generate("SELECT DISTINCT a.nom_nom FROM mat_obr a, mat_obr b WHERE a.nom_nom=b.nom_nom AND a.vo='32' AND b.vo='52' AND a.kol_d=-1*b.kol_d AND a.cen_zav<>b.cen_zav AND a.ceh=b.sklad AND b.ceh=a.sklad")
	nom_g=mat_obr.table.base.generate("SELECT DISTINCT nom_nom, sklad FROM mat_obr WHERE (kol_i=0 and sum_i!=0) or (kol_i!=0 and sum_i=0) or (kol=0 and sum_zav!=0) or (kol!=0 and sum_zav=0) or kol_i<0 or sum_i<0 or kol<0 or sum_zav<0")
	nom_list=[]
	fifty2_list=fifty2_noms()
	for nom in nom_g:
		if nom[0] not in fifty2_list:
			nom_list+=[nom]
	
	err_list=[]
	for nom in nom_list:
		err_g=mat_obr.table.base.generate("SELECT blk_no, nom_nom, sklad, ceh, zak, ei, cen_zav, vo, kol, sum_zav, ost_ksu, sum_post, sum_chet, kol_tn, cen_post, kol_d, sum_d, kol_i, sum_i, n_ksu, ost_ksud FROM mat_obr WHERE nom_nom='"+nom[0]+"' AND sklad='"+nom[1]+"'")
		for err in err_g:
			err_list+=[err]

	return err_list
	
	
def oborots():
	"This function returns rows where saldo_in+in-out!=saldo_out"
	mat_obr=atable()
	mat_obr.table.base.dsn='cen'
	#nom_g=mat_obr.table.base.generate("SELECT DISTINCT a.nom_nom FROM mat_obr a, mat_obr b WHERE a.nom_nom=b.nom_nom AND a.vo='32' AND b.vo='52' AND a.kol_d=-1*b.kol_d AND a.cen_zav<>b.cen_zav AND a.ceh=b.sklad AND b.ceh=a.sklad")
	nom_g=mat_obr.table.base.generate("SELECT DISTINCT nom_nom FROM mat_obr WHERE (kol+kol_d!=kol_i) OR (sum_zav+sum_d!=sum_i)")
	nom_list=[]
	for nom in nom_g:
		nom_list+=[nom]
	
	err_list=[]
	for nom in nom_list:
		err_g=mat_obr.table.base.generate("SELECT blk_no, nom_nom, sklad, ceh, zak, ei, cen_zav, vo, kol, sum_zav, ost_ksu, sum_post, sum_chet, kol_tn, cen_post, kol_d, sum_d, kol_i, sum_i, n_ksu, ost_ksud FROM mat_obr WHERE nom_nom='"+nom+"'")
		for err in err_g:
			err_list+=[err]

	return err_list
	

def no_job_mat():
	"This function checks zero mat norms"
	#beginning of function suite
	no_job=atable()
	no_job.table.base.dsn='cen'
	no_job.table.base.netdrive='V:'
	no_job.name='no_job'
	err_g=no_job.generate(['Ceh', 'Uch', 'Zak', 'Nchert', 'Kol_det', 'Nomnom', 'N_opr5', 'Normar', 'Chist', 'Chern', 'Tst', 'Tpz', 'Cena'],[['Normar','=',0], ['OR Chist','=',0], ['OR Cena','=',0]])
	err_list=[]
	for err in err_g:
			err_list+=[err]
	#end of function suite
	return err_list

def no_job_trud():
	"This function checks zero trud norms"
	#beginning of function suite
	no_job=atable()
	no_job.table.base.dsn='cen'
	no_job.table.base.netdrive='V:'
	no_job.name='no_job'
	err_g=no_job.generate(['Ceh', 'Uch', 'Zak', 'Nchert', 'Kol_det', 'Nomnom', 'N_opr5', 'Normar', 'Chist', 'Chern', 'Tst', 'Tpz', 'Cena'],[['Tst','=',0],['OR Tpz','=',0]])
	err_list=[]
	for err in err_g:
			err_list+=[err]
	#end of function suite
	return err_list


def no_job_all():
	"This function generates all records"
	#beginning of function suite
	no_job=atable()
	no_job.table.base.dsn='cen'
	no_job.table.base.netdrive='V:'
	no_job.name='no_job'
	err_g=no_job.generate(['Ceh', 'Uch', 'Zak', 'Nchert', 'Kol_det', 'Nomnom', 'N_opr5', 'Normar', 'Chist', 'Chern', 'Tst', 'Tpz', 'Cena'])
	err_list=[]
	for err in err_g:
			err_list+=[err]
	#end of function suite
	return err_list


#def nzp_trud()
